package org.myframework.jdbc;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.myframework.jdbc.cache.Cache;
import org.myframework.jdbc.cache.impl.EhcacheCache;
import org.myframework.jdbc.dialect.Dialect;
import org.myframework.jdbc.sqlconfig.SqlMapper;
import org.myframework.jdbc.sqltemplate.SqlTemplate;
import org.myframework.jdbc.sqltemplate.impl.VelocitySqlTemplate;
import org.myframework.util.MapUtils;
import org.myframework.util.StringUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.jdbc.core.namedparam.ParsedSql;

/**
 * 1.SQL可配置可热部署
 * <ol>
 * <li>{@see  ISimpleCrud}
 * <li>{@see  INativeQuery}
 * </ol>
 * @author wanghui
 * @since 1.0
 */
public class JpaNativeQuery
		implements  NativeQuery
{
	private static Log log = LogFactory
			.getLog(JpaNativeQuery.class);


	private static int DEFAULT_PAGE_SIZE = 50;

	protected Cache dataCache = new EhcacheCache(getClass().getName());

	private SqlTemplate sqlTemplate = new VelocitySqlTemplate();

	/**
	 *  SPRING 获取JPA entityManager的两种方式
	 *  LocalContainerEntityManagerFactoryBean entityManagerFactory = ctx.getBean(LocalContainerEntityManagerFactoryBean.class);
        entityManager = entityManagerFactory.nativeEntityManagerFactory.createEntityManager();

        EntityManager entityManager = ctx.getBean(EntityManager.class);
        System.out.println(entityManager);
	 */
	private EntityManager entityManager ;

	/**
	 *
	 * @param mapperId
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> selectAllList(String sqlKey,
			Map<String, Object> map) {
		String cacheKey = getCacheKey(sqlKey, map);
		SqlMapper sqlMapper = sqlTemplate.getSqlMapper(sqlKey);
		String sql = sqlTemplate.getSql(sqlKey, map);
		if (sqlMapper == null) {
			throw new RuntimeException("load sqlconfig error :" + sqlKey);
		}
		if (sqlMapper.isFlushCache()) {
			dataCache.removeObject( cacheKey);
		}
		if (sqlMapper.isUseCache()) {
			if (dataCache.getObject(cacheKey) != null) {
				log.debug("load from cacheKey : " + cacheKey);
				return (List<Map<String, Object>>) dataCache
						.getObject (cacheKey);
			} else {
				log.debug("reload   cacheKey : " + cacheKey);
				List<Map<String, Object>> rs = this.queryForList(sql, map);
				dataCache.putObject(cacheKey, rs);
				return rs;
			}
		} else {
			List<Map<String, Object>> rs = this.queryForList(sql, map);
			return rs;
		}
	}

	/**
	 * @param sqlKey
	 * @param map
	 * @param offset
	 * @param limit
	 * @return offset+1~offset+limit
	 */
	public List<Map<String, Object>> selectPageList(String sqlKey,
			Map<String, Object> map, int offset, int limit) {
		log.debug("offset " + offset + " limit " + limit);
		String sql = sqlTemplate.getSql(sqlKey, map);
		return queryPageList(sql,map,offset,limit);
	}



	/**
	 * @param sqlKey
	 * @param map
	 * @param offset
	 * @param limit
	 * @return offset+1~offset+limit
	 * @throws SQLException
	 */
	public List<Map<String, Object>> selectLogicPageList(String sql,
			Map<String, Object> map, int offset, int limit) {
		log.debug("offset " + offset + " limit " + limit);
		log.debug(sql);
		List<Map<String, Object>> rsList = new ArrayList<Map<String, Object>>(
				limit);
		List<Map<String, Object>> ls = this.queryForList(sql, map);
		// offset+1~offset+limit
		for (int rownum = 0; rownum < ls.size() && rownum <= offset + limit; rownum++) {
			log.debug(ls.get(rownum));
			if (rownum >= offset + 1 && rownum <= offset + limit) {
				log.debug("add ...." + ls.get(rownum));
				rsList.add(ls.get(rownum));
			}
		}
		return rsList;
	}

	/**
	 *
	 * @param sqlkey
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>> selectPageList(String sqlkey,
			Map<String, Object> map) {
		MapUtils<String, Object> rm = new MapUtils<String, Object>(map);
		int offset = rm.getInt(Dialect.OFFSET);
		int limit = rm.hasKey(Dialect.LIMIT) ? rm.getInt(Dialect.LIMIT)
				: DEFAULT_PAGE_SIZE;
		return this.selectPageList(sqlkey, map, offset, limit);
	}

	/**
	 *
	 * @param mapperId
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public Long selectCount(String sqlKey, Map<String, Object> map) {
		String sql =   String.format( "select count(1) from ( %s )" ,sqlTemplate.getSql(sqlKey, map));
		return this.queryForObject(sql, map, Long.class);
	}



	public String getSql(String sqlKey, Map<String, Object> map) {
		String sql = sqlTemplate.getSql(sqlKey, map);
		return "[" + sqlKey + "]" + getExecuteSql(sql, map);
	}

	public String getExecuteSql(String sql, Map<String, Object> map) {
		String convertSql = sqlConvert(sql, map);

		ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
		MapSqlParameterSource paramSource = new MapSqlParameterSource(map);
		String sqlToUse = NamedParameterUtils.substituteNamedParameters(
				parsedSql, paramSource);
		Object[] params = NamedParameterUtils.buildValueArray(parsedSql,
				paramSource, null);
		List<Object> paramList = Arrays.asList(params);
		String executeSql = getSql(sqlToUse, paramList);
		log.debug("sql :" + sql);
		log.debug("convertSql :" + convertSql);
		log.debug("parsedSql :" + parsedSql);
		log.debug("sqlToUse :" + sqlToUse);
		log.debug("params :" + paramList);
		log.debug("executeSql :" + executeSql);
		return executeSql;
	}

	/**
	 * sql ( select * from table where user_id = :userId ) Convert to standard
	 * jdbc sql ( select * from table where user_id = ?) ;
	 *
	 * @param sql
	 * @param map
	 * @param list
	 * @return
	 */
	@SuppressWarnings("all")
	private String sqlConvert(String sql, Map qryMap) {
		// replace #param to value
		if (qryMap != null && !qryMap.isEmpty()) {
			List<String> params = getReplaceParameterList(sql);
			for (String param : params) {
				sql = StringUtils.replaceOnce(sql, "#" + param,
						StringUtils.asString(qryMap.get(param)));
			}
		}
		return sql;
	}

	private String getSql(String sql, List<Object> lstParam) {
		for (int i = 0; i < lstParam.size(); i++) {
			Object value = lstParam.get(i);
			if (value instanceof Integer || value instanceof Double
					|| value instanceof Float || value instanceof Long) {
				sql = StringUtils.replaceOnce(sql, "?", value.toString());
			} else if (value instanceof String) {
				sql = StringUtils.replaceOnce(sql, "?", "'" + value.toString()
						+ "'");
			} else {
				sql = StringUtils.replaceOnce(sql, "?",
						"'" + String.valueOf(value) + "'");
			}
		}
		return sql;
	}

	private String getCacheKey(String sqlKey, Map<String, Object> qryMap) {
		StringBuffer cacheKey = new StringBuffer(sqlKey);
		if (qryMap != null && !qryMap.isEmpty())
			cacheKey.append(cacheKey.toString()).append("_")
					.append(qryMap.toString());
		return cacheKey.toString();
	}

	/**
	 * sql ( select * from table where user_id = #userId ) #userId will be
	 * replaced into list as param
	 *
	 * @param sql
	 * @return
	 */
	private List<String> getReplaceParameterList(String sql) {
		String regex = "\\#(\\w+)";
		Pattern pattern = Pattern.compile(regex);
		Matcher matcher = pattern.matcher(sql);
		List<String> params = new ArrayList<String>();
		while (matcher.find())
			params.add(matcher.group(1));
		return params;
	}

	@Override
	public   List<Map<String, Object>> queryForList(String sql,
			Map<String, ?> map){
		Query query = getSQLQuery(  sql,  map);
		return query.getResultList();
	}

	@Override
	public Map<String, Object> queryForMap(String sql, Map<String, ?> paramMap) {
		Query query = getSQLQuery(  sql,  paramMap);
		return  (Map<String, Object>)query.getSingleResult() ;
	}

	@Override
	public <T> T queryForObject(String sql, Map<String, ?> map,
			Class<T> requiredType) {
		Query query = entityManager.createNativeQuery(sql);
      	for (String key : map.keySet())
      		query.setParameter(key,map.get(key));
		return requiredType.cast(query.getSingleResult());
	}


	/**
	 * 使用Hibernate内置的分页查询功能
	 * @param sql
	 * @param map
	 * @param offset
	 * @param limit
	 * @return
	 */
	private List<Map<String, Object>> queryPageList(String sql,
			Map<String, Object> map, int offset, int limit){
			Query query = getSQLQuery(  sql,  map);
 			query.setFirstResult(offset);// 设置查询结果的开始记录数
 			query.setMaxResults(offset+limit);// 设查询结果的结束记录数 查询记录数1~~10的数据
			List<Map<String, Object>> rows = query.getResultList();
			return rows ;
	}

	private Query  getSQLQuery(String sql,
			Map<String, ?> map){
		Query query = entityManager.createNativeQuery(sql);

      	for (String key : map.keySet())
      		query.setParameter(key,map.get(key));
		query.unwrap(SQLQuery.class).setResultTransformer(
				Transformers.ALIAS_TO_ENTITY_MAP);
		return query;
	}


	public void setDataCache(Cache dataCache) {
		log.debug("setDataCache" + dataCache);
		this.dataCache = dataCache;
	}

	public void setSqlTemplate(SqlTemplate sqlTemplate) {
		this.sqlTemplate = sqlTemplate;
	}

	public JpaNativeQuery( ) {
		super();
	}




}
